alter table [Contract]
add TravelId int null
go
update [Contract]
set TravelId = ClientPackage.TravelId
from ClientPackage
where [Contract].PackageId = ClientPackage.PackageId
	and [Contract].ContactId = ClientPackage.ClientId
go	
ALTER PROCEDURE [dbo].[spContract_SetStatus]
(
	@ContractId int,
	@Status varchar(50),
	
	@ContractLinkValidDays int = null,
	@IP varchar(20) = null,
	@Pin varchar(4) out
)
AS
BEGIN
	set nocount on
	set xact_abort on
	
	if (@Status = 'Sent')
	begin
		declare @LinkCode varchar(255), @ExpiredLinkDate datetime
		set @LinkCode = CONVERT(varchar(255), NEWID())
		set @ExpiredLinkDate = DATEADD(day, @ContractLinkValidDays, convert(date, GETDATE()))
		
		-- if first code generation
		if exists(select 1 from [Contract] where (ContractId = @ContractId) and (LinkCode is null))
			update [Contract]
			set [Status] = @Status, ContractDate = CONVERT(date, getdate()),
				LinkCode = @LinkCode, ExpiredLinkDate = @ExpiredLinkDate
			where ContractId = @ContractId
		else
			update [Contract]
			set LinkCode = @LinkCode, ExpiredLinkDate = @ExpiredLinkDate
			where ContractId = @ContractId
	end
	
	if (@Status = 'Viewed')
	begin
		if exists(select 1 from [Contract] where (ContractId = @ContractId) and (ViewDate is null))
			update [Contract]
			set [Status] = @Status,
				ViewDate = GETDATE(), ViewIP = @IP
			where ContractId = @ContractId
	end
	
	if (@Status = 'Signed')
	begin
		if exists(select 1 from [Contract] where (ContractId = @ContractId) and (SignedDate is null))
		begin
			declare @PackageId int, @ContactId int, @ExpDate datetime, @TravelId int
			
			select @PackageId = PackageId, @ContactId = ContactId, @ExpDate = ExpDate
			from [Contract] (nolock)
			where ContractId = @ContractId
			
			if exists(select 1 from Contact (nolock) where (ContactId = @ContactId) and (Pin is null))
				exec spPin_Create @Pin = @Pin out
						
			begin transaction
				update [Contract]
				set [Status] = @Status,
					SignedDate = GETDATE(), SignedIP = @IP
				where ContractId = @ContractId
				
				-- if pin was generated, than write it
				if @Pin is not null
					update Contact
					set Pin = @Pin
					where (ContactId = @ContactId)
				
				if (@PackageId is not null)
				begin
					insert into ClientPackage
					(ClientId, [Status], StartDate, ExpDate, PackageId)
					values(@ContactId, 'AVAILABLE', GETDATE(), @ExpDate, @PackageId)
					
					set @TravelId = SCOPE_IDENTITY()
					
					update [Contract]
					set TravelId = @TravelId
					where ContractId = @ContractId
				end
			commit transaction
		end
	end
END
go
ALTER PROCEDURE [dbo].[spContract_Search]
(
	@AgentId int,
	@Status varchar(50),
	@ContactName varchar(50),
	@PackageStatus varchar(50)
)  
AS
begin
	select ctr.ContractId, ctr.ContractDate, ctr.ContactId, ctr.TemplateId, ctr.Status,
		ct.FirstName + ' ' + ct.LastName as ContactName,
		ctr.AgentId,
		-- agent - contract creator, not contact owner
		u.FirstName + ' ' + u.LastName as AgentName,
		ctr.ViewDate, ctr.ViewIP, ctr.SignedDate, ctr.SignedIP,
		tp.VacationLength, cp.[Status] as PackageStatus
    from [Contract] (nolock) ctr left join Contact (nolock) as ct on ctr.ContactId = ct.ContactId
									left join [User] (nolock) as u on ctr.AgentId = u.Id
									left join TravelPackage (nolock) as tp on ctr.PackageId = tp.PackageId
									left join ClientPackage (nolock) as cp on ctr.TravelId = cp.TravelId
    where ((ct.AgentId = @AgentId) or (ctr.AgentId = @AgentId) or (@AgentId is null))
		and ((ctr.[Status] = @Status) or (@Status is null))
		and ((cp.[Status] = @PackageStatus) or (@PackageStatus is null))
		and ((ct.LastName LIKE @ContactName + '%') or (@ContactName is null))
end
go
ALTER PROCEDURE [dbo].[spContract_SetBooked]
(
	@ContractId int,
	
	@ChoiceIdList varchar(max) = null,
	@OverrideChoiceList varchar(max) = null
)
AS
BEGIN
	set nocount on
	set xact_abort on
		
	declare @TravelId int
	
	select @TravelId = TravelId 
	from [Contract] (nolock)
	where ContractId = @ContractId
	
	begin transaction
		update ClientPackage
		set [Status] = 'BOOKED'
		where TravelId = @TravelId
		
		if @ChoiceIdList is not null
			update ClientPackageChoice
			set OverrideChoice = vwOverride.Item
			from dbo.fnStringSplit(@ChoiceIdList, ';') as vwId inner join dbo.fnStringSplit(@OverrideChoiceList, ';') as vwOverride on vwId.IdItem = vwOverride.IdItem
			where ClientPackageChoice.TravelId = @TravelId
				and ClientPackageChoice.ChoiceId = Convert(int, vwId.Item)
	commit transaction
END
go
ALTER PROCEDURE [dbo].[spContract_SetCanceled]
(
	@ContractId int
)
AS
BEGIN
	set nocount on
	set xact_abort on
		
	declare @TravelId int
	
	select @TravelId = TravelId
	from [Contract] (nolock)
	where ContractId = @ContractId
		
	update ClientPackage
	set [Status] = 'CANCELED' 
	where TravelId = @TravelId
END
go
ALTER PROCEDURE [dbo].[spContract_SetClear]
(
	@ContractId int
)
AS
BEGIN
	set nocount on
	set xact_abort on
		
	declare @TravelId int
	
	select @TravelId = TravelId
	from [Contract] (nolock)
	where ContractId = @ContractId
		
	begin transaction
		delete
		from ClientPackageChoice
		where TravelId = @TravelId
	
		update ClientPackage
		set [Status] = 'AVAILABLE', 
			PkgDestId = NULL
		where TravelId = @TravelId
	commit transaction
END
go